﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using Common;
using Model;
using BLL;
using WebPortal.Models;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Net;
using System.Web.Script.Serialization;

namespace WebPortal.Controllers
{
    public class ViewGroupController : BaseController
    {
        #region List
        public ActionResult List()
        {
            return View();
        }
        public ActionResult TargetData()
        {
            return View();
        }

        [HttpPost]
        public ActionResult List(string groupName, string systemID, string dbName, int pageSize = 10, int pageIndex = 1)
        {
            #region 参数与变量

            pageSize = int.Parse(Request.Params["rows"]);
            pageIndex = int.Parse(Request.Params["page"]);
            string order = Request.Params["order"];
            string sort = Request.Params["sort"];
            int totalCount = 0;

            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            SysViewGroupBLL viewGroupBLL = new SysViewGroupBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);

            List<GroupModel> groupModellist = new List<GroupModel>();
            #endregion

            #region 检查对接系统

            var targetSystemInfo = bll.GetInfoById(systemID);
            if (targetSystemInfo == null || targetSystemInfo.GUID.IsNullOrEmpty())
            {
                return Json(new { Success = false, Message = "对接系统不存在，无法读取数据" });
            }
            #endregion

            #region 加载推送组

            var viewGrouplist = viewGroupBLL.GetViewGroupBySysID(systemID, groupName, sort, order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase), -1, -1, out totalCount);

            var viewGroupModellist = (from groupInfo in viewGrouplist
                                      select new GroupModel()
                                      {
                                          GroupID = groupInfo.ID,
                                          GroupName = groupInfo.GroupName,
                                          Description = groupInfo.Description,
                                          CreateTime = groupInfo.CreateTime.ToString("yyyy-MM-dd"),
                                          Status = groupInfo.Status,
                                          GroupType = 1
                                      }).ToList();
            #endregion

            #region 加载对接系统下的模型

            var viewConfiglist = viewConfigBll.GetViewConfigBySysIdANDNotForGroup(systemID);

            var viewConfigModellist = (from groupInfo in viewConfiglist
                                       where groupName.IsNullOrEmpty()
                                       || (!groupName.IsNullOrEmpty() &&
                                       (groupInfo.PushName.IsNullOrEmpty() && groupInfo.ViewName.Equals(groupName, StringComparison.CurrentCultureIgnoreCase))
                                       || (!groupInfo.PushName.IsNullOrEmpty() && groupInfo.PushName.Equals(groupName, StringComparison.CurrentCultureIgnoreCase)))
                                       select new GroupModel()
                                       {
                                           GroupID = groupInfo.Guid,
                                           GroupName = groupInfo.PushName.IsNullOrEmpty() ? groupInfo.ViewName : groupInfo.PushName,
                                           Description = groupInfo.ViewDescription,
                                           CreateTime = groupInfo.CreateTime,
                                           Status = 1,
                                           GroupType = 2
                                       }).ToList();

            #endregion

            #region 混合整理数据：排序（保证推送组在前面）

            if (sort.Equals("GroupName", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    viewGroupModellist = viewGroupModellist.OrderBy(p => p.GroupName).ToList();
                    viewConfigModellist = viewConfigModellist.OrderBy(p => p.GroupName).ToList();
                }
                else
                {
                    viewGroupModellist = viewGroupModellist.OrderByDescending(p => p.GroupName).ToList();
                    viewConfigModellist = viewConfigModellist.OrderByDescending(p => p.GroupName).ToList();
                }
            }
            if (sort.Equals("Description", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    viewGroupModellist = viewGroupModellist.OrderBy(p => p.Description).ToList();
                    viewConfigModellist = viewConfigModellist.OrderBy(p => p.Description).ToList();
                }
                else
                {
                    viewGroupModellist = viewGroupModellist.OrderByDescending(p => p.Description).ToList();
                    viewConfigModellist = viewConfigModellist.OrderByDescending(p => p.Description).ToList();
                }
            }
            if (sort.Equals("CreateTime", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    viewGroupModellist = viewGroupModellist.OrderBy(p => p.CreateTime).ToList();
                    viewConfigModellist = viewConfigModellist.OrderBy(p => p.CreateTime).ToList();
                }
                else
                {
                    viewGroupModellist = viewGroupModellist.OrderByDescending(p => p.CreateTime).ToList();
                    viewConfigModellist = viewConfigModellist.OrderByDescending(p => p.CreateTime).ToList();
                }
            }

            groupModellist.AddRange(viewGroupModellist);
            groupModellist.AddRange(viewConfigModellist);
            if (pageIndex > -1 && pageSize > -1)
            {
                groupModellist = groupModellist.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }

            #endregion

            var jsonDict = new Dictionary<String, Object> { { "total", totalCount }, { "rows", groupModellist } };
            return Json(jsonDict);
        }

        /// <summary>
        /// 将对接系统下的模型添加到推送组
        /// </summary>
        /// <param name="groupName"></param>
        /// <param name="systemID"></param>
        /// <param name="dbName"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult AddModelsToGroup(string groupID, string systemID, string dbName, FormCollection formCollection)
        {
            #region 参数与变量

            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;

            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            SysViewGroupBLL viewGroupBLL = new SysViewGroupBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);

            if (groupID.IsNullOrEmpty())
            {
                return Json(new
                {
                    Success = false,
                    Message = "参数推送组ID为空"
                });
            }

            var viewIDs = formCollection["viewIDs"];
            if (viewIDs.IsNullOrEmpty())
            {
                return Json(new
                {
                    Success = false,
                    Message = "提交的视图模型为空"
                });
            }
            var viewIDList = viewIDs.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();

            #endregion

            #region 将对接系统下的模型添加到推送组

            var result = viewConfigBll.AddModelsToGroup(viewIDList, groupID);
            if (result > 0)
            {
                return Json(new
                {
                    Success = true,
                    Message = ("成功将 {0} 个视图模型添加到推送组中").Format(result)
                });
            }
            else
            {
                return Json(new
                {
                    Success = false,
                    Message = "添加失败"
                });
            }
            #endregion
        }

        #endregion

        #region Add

        public ActionResult Add(string groupID, string systemID, string dbName)
        {
            SysViewGroup model = null;
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            if (!systemID.IsNullOrEmpty())
            {

                var targetSystemInfo = bll.GetInfoById(systemID);
                ViewBag.TargetSysName = targetSystemInfo.TargetSysName;
            }
            if (!groupID.IsNullOrEmpty())
            {
                SysViewGroupBLL groupBll = new SysViewGroupBLL(loginInfos);

                model = groupBll.GetViewGroupByGroupID(groupID);
                if (model == null)
                {
                    model = new SysViewGroup();
                    model.SysID = systemID;
                }
            }
            else
            {
                model = new SysViewGroup();
                model.SysID = systemID;
            }
            return View(model);

        }

        public ActionResult Update(string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            int resultCount = 0;
            SysViewGroupBLL groupBll = new SysViewGroupBLL(loginInfos);

            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            SysViewGroup model = (SysViewGroup)jsSerializer.Deserialize(Request.Params["query"], typeof(SysViewGroup));
            string paras = Request.Params["paras"];
            model.Status = 1;
            try
            {
                if (!model.ID.IsNullOrEmpty())//更新信息
                {
                    var list = groupBll.GetViewGroupBySysIDWithOutGUID(model.SysID, model.ID, model.GroupName);
                    if (list.Count > 0)
                    {
                        return Json(new
                        {
                            Success = false,
                            Message = "推送组名称存在相同项"
                        });
                    }
                    resultCount = groupBll.Update(model);
                }
                else//添加信息
                {
                    var list = groupBll.GetViewGroupBySysID(model.SysID, model.GroupName);
                    if (list.Count > 0)
                    {
                        return Json(new
                        {
                            Success = false,
                            Message = "推送组名称存在相同项"
                        });
                    }
                    string id = Guid.NewGuid().ToString();
                    model.ID = id;
                    resultCount = groupBll.Insert(model);
                }
                if (resultCount == 1)
                {
                    if (!string.IsNullOrEmpty(paras))
                    {
                        if (groupBll.DeleteCallBack(model.ID))//删除旧记录
                        {
                            #region 回调配置
                            EtlSourceSystemBLL sourcesystemBll = new EtlSourceSystemBLL(loginInfos);
                            IList<ETLCallBackConfig> cbList = (IList<ETLCallBackConfig>)jsSerializer.Deserialize(paras, typeof(IList<ETLCallBackConfig>));
                            foreach (ETLCallBackConfig cbConfig in cbList)
                            {
                                cbConfig.GUID = Guid.NewGuid().ToString();
                                cbConfig.SysType = 2;
                                cbConfig.SysID = model.ID;
                                sourcesystemBll.AddCallBack(cbConfig);//添加回调配置
                                string para = cbConfig.Paras;
                                string paraValues = cbConfig.ParaValues;
                                if (!string.IsNullOrEmpty(para))
                                {
                                    List<string> paraArr = para.Split('&').ToList();
                                    List<string> paraValuesArr = paraValues.Split('&').ToList();
                                    for (int i = 0; i < paraArr.Count; i++)
                                    {
                                        ETLCallBackPara cbp = new ETLCallBackPara()
                                        {
                                            GUID = Guid.NewGuid().ToString(),
                                            KeyName = paraArr[i],
                                            KeyValue = paraValuesArr[i],
                                            ParentGUID = cbConfig.GUID
                                        };
                                        sourcesystemBll.AddCallBackParas(cbp);//添加回调配置参数
                                    }
                                }

                            }
                            #endregion

                        }
                    }

                    return Json(new
                    {
                        Success = true,
                        Message = "保存成功"
                    });
                }
                else
                {
                    return Json(new
                    {
                        Success = false,
                        Message = "保存推送组失败"
                    });
                }
            }
            catch (Exception ex)
            {
                return Json(new
                        {
                            Success = false,
                            Message = ex.Message
                        });
            }
        }

        public ActionResult Delete(string groupID, FormCollection collection, string dbName)
        {
            #region 变量信息
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            int resultCount = 0;
            SysViewGroupBLL groupBll = new SysViewGroupBLL(loginInfos);

            #endregion

            #region 参数检查

            if (groupID.IsNullOrEmpty())
            {
                return Json(new
                {
                    Success = false,
                    Message = "删除失败（参数错误）"
                });
            }
            #endregion
            resultCount = groupBll.Delete2(groupID);
            if (resultCount == 1)
            {
                return Json(new
                {
                    Success = true,
                    Message = "删除成功"
                });
            }
            else
            {
                return Json(new
                {
                    Success = false,
                    Message = "删除失败（请刷新后重试）"
                });
            }
        }

        #endregion

        #region ModelList

        public ActionResult ModelList()
        {
            return View();
        }

        public ActionResult GetModelList(string systemID)
        {
            #region 参数与变量

            string tableName = Request.Params["tableName"];
            string dbName = Request.Params["dbName"];

            string order = Request.Params["order"];
            string sort = Request.Params["sort"];

            if (string.IsNullOrEmpty(sort))
                sort = "CreateTime";
            // string rows = Request.Params["rows"];
            // string page = Request.Params["page"];
            int pageSize = int.Parse(Request.Params["rows"]);
            int pageIndex = int.Parse(Request.Params["page"]);

            string expIDs = Request.Params["ExpIDs"];
            if (string.IsNullOrEmpty(expIDs))
                expIDs = "";
            //列表数据
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ModelConfigBLL bll = new ModelConfigBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);

            #endregion

            #region 加载配置到对接系统下的模型数据

            var viewConfiglist = viewConfigBll.GetViewConfigBySysIdANDNotForGroup(systemID);
            var expIDList = expIDs.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            var result = (from viewInfo in viewConfiglist
                          where (!expIDList.Contains(viewInfo.Guid, (t1, t2) =>
                              {
                                  if (t1.Equals(t2, StringComparison.CurrentCultureIgnoreCase))
                                      return true;
                                  else
                                      return false;
                              }))
                          select viewInfo).ToList();

            #endregion

            #region 加载配置到对接系统下的模型数据

            if (sort.Equals("GroupName", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    result = result.OrderBy(p => p.PushName).ToList();
                }
                else
                {
                    result = result.OrderByDescending(p => p.PushName).ToList();
                }
            }
            if (sort.Equals("ViewDescription", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    result = result.OrderBy(p => p.ViewDescription).ToList();
                }
                else
                {
                    result = result.OrderByDescending(p => p.ViewDescription).ToList();
                }
            }
            if (sort.Equals("CreateTime", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    result = result.OrderBy(p => p.CreateTime).ToList();
                }
                else
                {
                    result = result.OrderByDescending(p => p.CreateTime).ToList();
                }
            }

            int total = result.Count;
            if (pageIndex > -1 && pageSize > -1)
            {
                result = result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            #endregion

            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", total);
            jsonDict.Add("rows", result);

            return Json(jsonDict);
        }


        #endregion

        public ActionResult GetTargetData()
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            try
            {
                string systemID = Request.Params["systemID"];
                string viewID = Request.Params["viewID"];
                string dbName = Request.Params["dbName"];
                string order = Request.Params["order"];
                string sort = Request.Params["sort"];
                string query = Request.Params["query"];
                List<string> sql = new List<string>();
                IDataParameter[] para = null;
                IDataParameter[] sqlPara = null;
                IDataParameter[] oledbPra = null;
                if (!string.IsNullOrEmpty(query) && query != "[]")
                {
                    query = query.Replace("\"", "").Replace("[[", "").Replace("]]", "");
                    IEnumerable<string> arrList = query.Split("],[");

                    sqlPara = new SqlParameter[arrList.Count()];
                    oledbPra = new OleDbParameter[arrList.Count()];
                    int i = 0;
                    foreach (string str in arrList)
                    {
                        string[] item = str.Split(',');
                        sql.Add(string.Format(" {0} {1} @{0}", item[0], item[1]));// " column=@column"
                        sqlPara[i] = new SqlParameter("@" + item[0], item[2]);
                        oledbPra[i] = new OleDbParameter("@" + item[0], item[2]);
                        i++;
                    }
                }

                if (string.IsNullOrEmpty(sort))
                    sort = "desc";
                if (string.IsNullOrEmpty(order))
                    order = "";

                LoginInfo loginInfos = base.loginInfo();
                loginInfos.DBName = dbName;
                EtlTargetSystemBLL targetBLL = new EtlTargetSystemBLL(loginInfos);
                ETL_Target_ViewConfigBLL viewBll = new ETL_Target_ViewConfigBLL(loginInfos);
                EtlSourceSystemBLL sourceSystemBLL = new EtlSourceSystemBLL(loginInfos);
                EtlTargetSystemInfo sysModel = targetBLL.GetInfoById(systemID);
                ETL_Target_ViewConfigInfo viewInfo = viewBll.GetTargetViewConfigInfoByGuid(viewID);

                string viewName = string.IsNullOrEmpty(viewInfo.PushName) ? viewInfo.ViewName : viewInfo.PushName;
                int connType = sysModel.TargetType;
                EncrypCode encrypCode = new EncrypCode();
                string connstr = encrypCode.Decrypto(sysModel.Connstr);// 系统数据库连接字符串 

                var dbConfigList = sourceSystemBLL.GetDbConfigBySysID(systemID);
                connstr = encrypCode.Decrypto(dbConfigList[0].DbConnectionStr);//源系统数据库连接字符串
                connType = dbConfigList[0].DbType;//数据库类型

                if (!string.IsNullOrEmpty(connstr))
                {
                    if (connType == 6)//平面文件
                    {
                        connstr = dbConfigList[0].DbDataSource;

                        jsonResponse.Data = ReadTxtData(connstr, viewName, dbConfigList[0].DbUserID, dbConfigList[0].DbPassword, 0, 0, "\\r\\n", "", ",");
                    }
                    else
                    {
                        if (connType == 3 || connType == 5)
                        {
                            connstr = dbConfigList[0].DbDataSource;
                            if (connstr.StartsWith("ftp"))
                            {
                                connstr = string.Format("/files/{0}/{1}", systemID.Replace("-", ""), connstr.Substring(connstr.LastIndexOf("/") + 1));
                                connstr = Server.MapPath(connstr);
                            }
                            para = oledbPra;
                        }
                        else
                            para = sqlPara;

                        DataTable dt = sourceSystemBLL.GetSourceTableData(viewName, connstr, connType.ToString(), order, sort, sql.ToArray(), para);
                        jsonResponse.Data = this.DataTableToString(dt, 0, order, sort);
                        jsonResponse.Head = GetSourceTableHeadData(dt.Columns);
                    }
                    jsonResponse.Success = true;
                    jsonResponse.Message = "OK";
                }
                else
                {
                    jsonResponse.Success = false;
                    jsonResponse.Message = "空的数据库连接，无法读取数据";
                    jsonResponse.Data = "";
                }
                return Json(jsonResponse);
            }
            catch (Exception ex)
            {
                //return "信息获取失败！" + ex.Message;
                jsonResponse.Success = false;
                jsonResponse.Message = "信息获取失败！" + ex.Message;
                jsonResponse.Data = "";
                return Json(jsonResponse);
            }
        }
        /// <summary>
        /// sortBy : 1 小到大
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sortField"></param>
        /// <param name="sortBy"></param>
        /// <returns></returns>
        private string DataTableToString(DataTable dt, int width, string sortField, string sortBy)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("");
            sb.AppendLine(string.Format("<table id=\"tbSoutceTableData\" class=\"pagTable\" cellspacing=\"0\" cellpadding=\"0\" style=\"{0}\">", (width > 0 ? string.Format("width: {0}px;", width) : string.Empty)));
            sb.AppendLine("<tbody>");
            sb.AppendLine(" <tr class=\"pagTablehd\">");
            int columnWidth = 100 / dt.Columns.Count;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string textalign = "";
                if (dt.Columns[i].DataType.Equals(typeof(int))
                    || dt.Columns[i].DataType.Equals(typeof(float))
                    || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                {
                    textalign = "right";
                }
                else
                {
                    textalign = "left";
                }
                string columnName = dt.Columns[i].ColumnName;
                sb.AppendLine(string.Format("<td class=\"DataGrid2ViewName csscursor cssnowrap\" style=\"width: {0}%; text-align: {1};\" onclick=\"initSourceTableData('{2}','{3}')\">"
                    , columnWidth, textalign
                    , columnName
                    , sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("desc") ? "asc" : "desc") : "desc"
                    ));
                sb.AppendLine(string.Format("{1}{0}", columnName
                    , sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("desc") ? "<img src='/images/datagrid_sort_asc.gif'>" : "<img src='/images/datagrid_sort_desc.gif'>") : ""
                    ));
                sb.AppendLine("</td>");
            }
            sb.AppendLine(" </tr>");
            int rowNumber = 0;
            foreach (DataRow rowItem in dt.Rows)
            {
                if (rowNumber++ % 2 == 0)
                {
                    sb.AppendLine(" <tr>");
                }
                else
                {
                    sb.AppendLine(" <tr class=\"tr_c2\">");
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string textalign = "";
                    if (dt.Columns[i].DataType.Equals(typeof(int))
                        || dt.Columns[i].DataType.Equals(typeof(float))
                        || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        textalign = "right";
                    }
                    else
                    {
                        textalign = "left";
                    }

                    var value = rowItem[i] == null || string.IsNullOrEmpty(rowItem[i].ToString()) ? "&nbsp;" : rowItem[i].ToString();
                    var title = value.Equals("&nbsp;") ? "" : string.Format("内容：{0}\n长度：{1}", value, value.Length);
                    sb.AppendLine(string.Format("     <td style=\"text-align: {0};\" title=\"{1}\">", textalign, title));
                    //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        sb.AppendLine("     <nobr>");
                    }
                    sb.AppendLine(string.Format("         {0}", value));
                    //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        sb.AppendLine("     </nobr>");
                    }
                    sb.AppendLine("     </td>");
                }
                sb.AppendLine(" </tr>");
            }
            sb.AppendLine("</tbody>");
            sb.AppendLine("</table>");
            return sb.ToString();
        }
        public string GetSourceTableHeadData(DataColumnCollection columnList)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbOpt = new StringBuilder();
            LoginInfo loginInfos = base.loginInfo();
            IList<Operator> optList = OperatorBLL.GetList();
            sbOpt.Append("<option value=''>-请选择-</option>");
            foreach (Operator o in optList)
            {
                sbOpt.Append("<option>" + o.LegalOperator + "</option>");
            }
            sb.Append("<table class='pagTable' cellpadding='0' cellspacing='0'>");
            foreach (DataColumn dc in columnList)
            {
                sb.Append(string.Format("<tr id='{0}'><td>{0}</td><td width='80px'><select class='sel_{0}'>{1}</select></td><td width='160px'><input width='150px' type='text' class='txt_{0}'></td></tr>", dc.ColumnName, sbOpt.ToString()));
            }
            sb.Append("</table>");
            return sb.ToString();
        }
        public string ReadTxtData(string connstr, string viewName, string DbUserID, string DbPassword, int skipRowNum, int skipDataNum, string lineSeparator, string textQualifier, string columnSeparator)
        {
            Stream stream = null;
            WebResponse response = null;
            if (connstr.StartsWith("ftp"))
            {
                FtpWebRequest reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(connstr + "/" + viewName));
                reqFTP.Credentials = new NetworkCredential(DbUserID, DbPassword);
                reqFTP.KeepAlive = false;
                reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
                response = reqFTP.GetResponse();
                stream = response.GetResponseStream();
            }
            else
            {
                stream = System.IO.File.Open(connstr + "\\" + viewName, System.IO.FileMode.Open);
            }
            System.IO.StreamReader streamReader = new System.IO.StreamReader(stream, System.Text.Encoding.GetEncoding("gb2312"));
            string outputHtml = "<table id=\"tableSourceData\" class=\"pagTable cssnowrap\" cellspacing=\"0\" cellpadding=\"0\" ><tr class=\"pagTablehd\"><td>";
            string headLine = streamReader.ReadLine();
            outputHtml += headLine + "\r\n";
            List<string> columnList = new List<string>();
            int readLineCount = 10 + skipRowNum + skipDataNum - 1;
            for (int i = 1; i < readLineCount; i++)
            {
                if (i <= (skipRowNum + skipDataNum))
                    streamReader.ReadLine();//跳过
                else
                    outputHtml += streamReader.ReadLine() + "\r\n";
            }
            outputHtml += streamReader.ReadLine();
            if (response != null)
                response.Close();
            streamReader.Close();
            streamReader.Dispose();
            stream.Close();
            stream.Dispose();

            switch (lineSeparator)
            {
                case "\\r\\n":
                    outputHtml = outputHtml.Replace("\r\n", "</td></tr><tr><td>");
                    break;
                case "\\r":
                    outputHtml = outputHtml.Replace("\r", "</td></tr><tr><td>");
                    break;
                case "\\n":
                    outputHtml = outputHtml.Replace("\n", "</td></tr><tr><td>");
                    break;
                case "\\t":
                    outputHtml = outputHtml.Replace("\t", "</td></tr><tr><td>");
                    break;
                default:
                    outputHtml.Replace(lineSeparator, "</td></tr><tr><td>");
                    break;
            }
            //string columnTg = Request.Form["columnTg"].Trim();
            if (textQualifier == "<无>" || string.IsNullOrEmpty(textQualifier))
            {
                outputHtml = outputHtml.Replace(columnSeparator, "&nbsp;</td><td>");
                headLine = headLine.Replace(columnSeparator, "$");
                columnList = headLine.Split('$').ToList();
            }
            else
            {
                outputHtml = outputHtml.Replace(textQualifier + columnSeparator + textQualifier, "&nbsp;</td><td>").Replace(textQualifier, "");
                headLine = headLine.Replace(textQualifier + columnSeparator + textQualifier, "$").Replace(textQualifier, "");
                columnList = headLine.Split('$').ToList();
            }
            outputHtml += "</td></tr></table>";

            return outputHtml;
        }

        #region ViewModelInGroup

        public ActionResult ViewModelInGroup(string groupID, string systemID, string dbName)
        {
            PageRole role = new PageRole();
            role.role = SysPublicBLL.GetUserRole(Request.Cookies["MDM_UserName"].Value, dbName);
            return View(role);
        }

        [HttpPost]
        public ActionResult GetViewModelInGroup(string groupName, string groupID, string systemID, string dbName, int pageSize = 10, int pageIndex = 1)
        {
            pageSize = int.Parse(Request.Params["rows"]);
            pageIndex = int.Parse(Request.Params["page"]);
            string order = Request.Params["order"];
            string sort = Request.Params["sort"];

            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL pbll = new SysPublicBLL(loginInfos);
            DBDeploy dbInfo = pbll.GetDBInfo(dbName);
            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            EtlSourceViewConfigBLL sourceViewConfigBLL = new EtlSourceViewConfigBLL(loginInfos);
            SysViewGroupBLL viewGroupBLL = new SysViewGroupBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);

            List<ETL_Target_ViewConfigInfo> viewConfiglist = viewConfigBll.GetViewConfigBySysIdANDGroupID(systemID, groupID, pageSize, pageIndex);
            int count = 0;
            if (viewConfiglist.Count > 0)
                count = viewConfiglist[0].Count;
            foreach (ETL_Target_ViewConfigInfo model in viewConfiglist)
            {
                if (model.ConfigStatus == 0)//大数据或未配置
                    model.MDP_ViewName = "<img src='/images/tree_file.gif' />" + model.MDP_ViewName;
                else
                    model.MDP_ViewName = "<img src='/images/tree_file.gif' /><a href='#' onclick='openViewModelData(\"" + model.Guid + "\")'>" + model.MDP_ViewName + "</a>";

                ETL_Task_List_History etlStepModel = sourceViewConfigBLL.GetStepStatus(model.Guid);
                model.ETLStatus = etlStepModel.TaskStatus;
            }
            var jsonDict = new Dictionary<String, Object> { { "total", count }, { "rows", viewConfiglist } };
            return Json(jsonDict);
        }

        [HttpPost]
        public ActionResult UpdatePushName(string pushName, string schema, string viewID, string systemID, string dbName)
        {
            #region 参数与变量
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            #endregion

            #region 参数检查
            if (pushName.IsNullOrEmpty())
            {
                return Json(new { Success = false, Message = "请输入推送名称" });
            }
            #endregion

            #region 更新模型的推送名称
            var result = viewConfigBLL.UpdatePushName(viewID, schema, pushName);

            if (result == 1)
            {
                return Json(new { Success = true, Message = "更新成功" });
            }
            else
            {
                return Json(new { Success = false, Message = "更新失败" });
            }
            #endregion
        }

        [HttpPost]
        public ActionResult RemoveModelFromGroup(string viewID, string groupID, string systemID, string dbName)
        {
            #region 参数与变量
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            #endregion

            #region 参数检查
            if (viewID.IsNullOrEmpty())
            {
                return Json(new { Success = false, Message = "模型 ID 参数错误" });
            }
            #endregion

            #region 更新模型的推送名称
            var result = viewConfigBLL.RemoveModelFromGroup(viewID);

            if (result == 1)
            {
                return Json(new { Success = true, Message = "取消成功" });
            }
            else
            {
                return Json(new { Success = false, Message = "取消失败" });
            }
            #endregion
        }
        #endregion

        #region ViewModelPushConfig

        public ActionResult ViewModelPushConfig(string groupID, string systemID, string dbName)
        {
            var html = GetModelPushConfigHtml(groupID, systemID, dbName);
            ViewBag.ViewConfigHtml = html;
            return View();
        }

        public ActionResult UpdateViewModelPushConfig(string groupID, string systemID, string dbName, FormCollection formCollection)
        {
            #region 参数与变量
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysViewGroupBLL groupBll = new SysViewGroupBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);
            #endregion

            #region 检查参数
            var modelList = viewConfigBll.GetViewConfigBySysIdANDGroupID(systemID, groupID, 100, 1);
            if (modelList.Count == 0)
            {
                return Json(new { Success = false, Message = "该推送组没有选择模型" });
            }
            #endregion

            #region 更新数据

            var pushConfigList = ParsePushConfig(modelList, formCollection);
            var result = groupBll.UpdateModelPushConfigs(groupID, pushConfigList);

            #endregion
            return Json(new { Success = true, Message = "更新视图推送配置成功" });

        }


        [NonAction]
        public string GetModelPushConfigHtml(string groupID, string systemID, string dbName)
        {
            #region 参数与变量
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysViewGroupBLL groupBll = new SysViewGroupBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);
            #endregion

            #region 检查参数
            var modelList = viewConfigBll.GetViewConfigBySysIdANDGroupID(systemID, groupID, 100, 1);
            if (modelList.Count == 0)
            {
                return "";
            }
            #endregion

            #region 输出

            var pushList = groupBll.GetModelPushConfigByGroupID(groupID);
            System.Text.StringBuilder html = new System.Text.StringBuilder();
            int i = 0;
            foreach (var viewConfigInfo in modelList)
            {
                string vt = "0";
                switch (vt)
                {
                    case "0":
                        vt = "基础视图";
                        break;
                    case "1":
                        vt = "自定义视图";
                        break;
                    case "2":
                        vt = "上传视图";
                        break;
                }
                //判断选项选择状态
                string ssis = "";
                string ws = ""; ;
                string full = "";
                string dis = "";
                string delt = "";
                string ibd = "";
                var _temp = (from pushItem in pushList
                             where pushItem.ViewID.Equals(viewConfigInfo.Guid, StringComparison.CurrentCultureIgnoreCase)
                             select pushItem).FirstOrDefault();
                if (_temp != null)
                {
                    if (_temp.IsSSISPush.Equals(1))
                        ssis = "checked='checked'";
                    if (_temp.IsWebServicePush.Equals(1))
                        ws = "checked='checked'";
                    if (_temp.IsFullAmount.Equals(1))
                        full = "checked='checked'";
                    if (_temp.IsDisable.Equals(1))
                        dis = "checked='checked'";
                    if (_temp.DeletedType.Equals(1))
                        delt = "checked='checked'";
                    if (_temp.IsBackDelete.Equals(1))
                        ibd = "checked='checked'";
                }
                //string controlId = string.Format("_{0}", i);
                string controlId = string.Format("_{0}", viewConfigInfo.Guid);
                html.AppendLine("<tr>");
                // 模型名称
                html.AppendLine(string.Format("<td><input type=\"hidden\" name='ViewID{2}' value='{0}'/>{1}</td>", viewConfigInfo.Guid, viewConfigInfo.MDP_ViewName, controlId));
                // 推送名称
                html.AppendLine(string.Format("<td><input type=\"hidden\" name='ViewGroupID{3}' value='{0}'/>{1}.{2}</td>", groupID, viewConfigInfo.PushSchema, viewConfigInfo.PushName, controlId));
                // 视图类型
                //html.AppendLine(string.Format("<td>{0}</td>", vt));
                // IsSSISPush
                html.AppendLine(string.Format("<td><input type=\"checkbox\" name='IsSSISPush{1}' value='1' {0}/></td>", ssis, controlId));
                // IsWebServicePush
                html.AppendLine(string.Format("<td><input type=\"checkbox\" name='IsWebServicePush{1}' value='1' {0}/></td>", ws, controlId));
                // IsFullAmount
                html.AppendLine(string.Format("<td><input type=\"checkbox\" name='IsFullAmount{1}' value='1' {0}/></td>", full, controlId));
                // IsDisable
                //html.AppendLine(string.Format("<td><input type=\"checkbox\" name='IsDisable{1}' value='1' {0}/></td>", dis, controlId));
                // 物理删除
                html.AppendLine(string.Format("<td><input type=\"checkbox\" name='DeletedType{1}' value='1' {0}/></td>", delt, controlId));
                // IsBackDelete
                //html.AppendLine(string.Format("<td><input type=\"checkbox\" name='IsBackDelete{1}' value='1' {0}/></td>", ibd, controlId));
                html.AppendLine("</tr>");
                //html.Append(string.Format("",
                //    "", viewConfigInfo.ViewName, vt, viewConfigInfo.Guid, groupID, ssis, ws, full, dis, delt, ibd));
                i++;
            }
            #endregion
            return html.ToString();
        }

        [NonAction]
        protected List<ETL_Push_ViewGroup> ParsePushConfig(List<ETL_Target_ViewConfigInfo> viewConfigList, FormCollection form)
        {
            List<ETL_Push_ViewGroup> pushConfigList = new List<ETL_Push_ViewGroup>();
            foreach (var viewConfigItem in viewConfigList)
            {
                var pushConfigInfo = new ETL_Push_ViewGroup()
                {
                    GUID = Guid.NewGuid().ToString(),
                    ViewID = viewConfigItem.Guid,
                    ViewGroupID = viewConfigItem.GroupID
                };
                string controlId = string.Format("_{0}", viewConfigItem.Guid);
                var ctrlAttributes = form["IsSSISPush" + controlId];
                if (!String.IsNullOrEmpty(ctrlAttributes))
                {
                    //int isSSISPush = int.Parse(ctrlAttributes);
                    pushConfigInfo.IsSSISPush = 1;
                }
                ctrlAttributes = form["IsWebServicePush" + controlId];
                if (!String.IsNullOrEmpty(ctrlAttributes))
                {
                    pushConfigInfo.IsWebServicePush = 1;
                }
                ctrlAttributes = form["IsFullAmount" + controlId];
                if (!String.IsNullOrEmpty(ctrlAttributes))
                {
                    pushConfigInfo.IsFullAmount = 1;
                }
                ctrlAttributes = form["IsDisable" + controlId];
                if (!String.IsNullOrEmpty(ctrlAttributes))
                {
                    pushConfigInfo.IsDisable = 1;
                }
                ctrlAttributes = form["DeletedType" + controlId];
                if (!String.IsNullOrEmpty(ctrlAttributes))
                {
                    pushConfigInfo.DeletedType = 1;
                }
                ctrlAttributes = form["IsBackDelete" + controlId];
                if (!String.IsNullOrEmpty(ctrlAttributes))
                {
                    pushConfigInfo.IsBackDelete = 1;
                }
                pushConfigList.Add(pushConfigInfo);
            }
            return pushConfigList;
        }
        #endregion

        #region 选择推送组页面 SelectGroupList

        public ActionResult SelectGroupList()
        {
            return View();
        }

        public ActionResult GetSelectGroupList(string dbName, FormCollection formCollection)
        {
            string order = Request.Params["order"];
            string sort = Request.Params["sort"];
            if (string.IsNullOrEmpty(sort))
                sort = "CreateTime";
            string rows = Request.Params["rows"];
            string page = Request.Params["page"];
            string groupName = Request.Params["groupName"];
            string SysName = Request.Params["SysName"];
            string expIDs = Request.Params["ExpIDs"];
            int pageSize = 10;
            int pageIndex = 1;
            if (!string.IsNullOrEmpty(formCollection["rows"]))
            {
                int.TryParse(formCollection["rows"], out pageSize);
            }
            if (!string.IsNullOrEmpty(formCollection["page"]))
            {
                int.TryParse(formCollection["page"], out pageIndex);
            }

            //列表数据
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            int totalCount = 0;
            SysViewGroupBLL viewGroupBLL = new SysViewGroupBLL(loginInfos);

            var viewGrouplist = viewGroupBLL.GetViewGroupByGroupName(groupName, SysName);
            var expIDList = string.IsNullOrEmpty(expIDs) ? new string[0] : expIDs.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            var viewGroupModellist = (from groupInfo in viewGrouplist
                                      where !expIDList.Contains(groupInfo.ID, (t1, t2) =>
                                            {
                                                if (t1.Equals(t2, StringComparison.CurrentCultureIgnoreCase))
                                                    return true;
                                                else
                                                    return false;
                                            })
                                      select new GroupModel()
                                      {
                                          GroupID = groupInfo.ID,
                                          GroupName = groupInfo.GroupName,
                                          Description = groupInfo.Description,
                                          CreateTime = groupInfo.CreateTime.ToString("yyyy-MM-dd"),
                                          Status = groupInfo.Status,
                                          TargetSysName = groupInfo.TargetSysName,
                                          GroupType = 1
                                      }).ToList();


            #region 混合整理数据：排序（保证推送组在前面）

            if (sort.Equals("GroupName", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    viewGroupModellist = viewGroupModellist.OrderBy(p => p.GroupName).ToList();
                }
                else
                {
                    viewGroupModellist = viewGroupModellist.OrderByDescending(p => p.GroupName).ToList();
                }
            }
            if (sort.Equals("Description", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    viewGroupModellist = viewGroupModellist.OrderBy(p => p.Description).ToList();
                }
                else
                {
                    viewGroupModellist = viewGroupModellist.OrderByDescending(p => p.Description).ToList();
                }
            }
            if (sort.Equals("TargetSysName", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    viewGroupModellist = viewGroupModellist.OrderBy(p => p.TargetSysName).ToList();
                }
                else
                {
                    viewGroupModellist = viewGroupModellist.OrderByDescending(p => p.TargetSysName).ToList();
                }
            }
            if (sort.Equals("CreateTime", StringComparison.CurrentCultureIgnoreCase))
            {
                if (order.Equals("1") || order.Equals("asc", StringComparison.CurrentCultureIgnoreCase))
                {
                    viewGroupModellist = viewGroupModellist.OrderBy(p => p.CreateTime).ToList();
                }
                else
                {
                    viewGroupModellist = viewGroupModellist.OrderByDescending(p => p.CreateTime).ToList();
                }
            }
            totalCount = viewGroupModellist.Count;
            if (pageIndex > -1 && pageSize > -1)
            {
                viewGroupModellist = viewGroupModellist.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }

            #endregion

            var jsonDict = new Dictionary<String, Object> { { "total", totalCount }, { "rows", viewGroupModellist } };
            return Json(jsonDict);
        }


        #endregion

        #region GetCreateTbScript 建表语句

        public ActionResult ExportCreateTableScriptByGroupID(string groupID, string systemID, string dbName)
        {
            #region 参数与变量
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL pbll = new SysPublicBLL(loginInfos);
            DBDeploy DBModel = pbll.GetDBInfo(dbName);

            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            SysViewGroupBLL viewGroupBLL = new SysViewGroupBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);
            StringBuilder sbScript = new StringBuilder();
            #endregion

            #region 检查对接系统
            var targetSystemInfo = bll.GetInfoById(systemID);
            if (targetSystemInfo == null || targetSystemInfo.GUID.IsNullOrEmpty())
            {
                //return Json(new { Success = false, Message = "对接系统不存在，无法读取数据" });
                sbScript.AppendLine("对接系统不存在");
            }
            else
            {
                var viewConfigList = viewConfigBll.GetViewConfigBySysIdANDGroupID(systemID, groupID, 100, 1);
                foreach (var viewConfigItem in viewConfigList)
                {
                    string viewScript = viewConfigBll.GetCreateTbScript(viewConfigItem.Guid, targetSystemInfo.TargetType.ToString(), DBModel.DBType);
                    if (DBModel.DBType == 2) { //大数据
                        viewScript = viewScript.ToUpper().Replace("PRIMARY KEY", "");
                    }
                    sbScript.AppendLine(viewScript);
                    sbScript.AppendLine("----------------------------------------------------------------");
                    sbScript.AppendLine();
                }
            }

            #endregion

            System.IO.MemoryStream cvsOutPut = new System.IO.MemoryStream();
            byte[] data = Encoding.UTF8.GetBytes(sbScript.ToString());
            cvsOutPut.Write(data, 0, data.Length);
            return File(cvsOutPut.ToArray(), "text/plain", string.Format("CreateTableSql_{0}.sql", DateTime.Now.ToString("yyyyMMddHHmmss")));

        }

        public ActionResult ExportCreateTableScriptByViewID(string viewID, string systemID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL pbll = new SysPublicBLL(loginInfos);
            DBDeploy DBModel = pbll.GetDBInfo(dbName);
            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            SysViewGroupBLL viewGroupBLL = new SysViewGroupBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);
            StringBuilder sbScript = new StringBuilder();


            #region 检查对接系统
            var targetSystemInfo = bll.GetInfoById(systemID);
            if (targetSystemInfo == null || targetSystemInfo.GUID.IsNullOrEmpty())
            { 
                sbScript.AppendLine("对接系统不存在");
            }
            else
            {
                string viewScript = viewConfigBll.GetCreateTbScript(viewID, targetSystemInfo.TargetType.ToString(), DBModel.DBType);
                if (DBModel.DBType == 2)
                { //大数据
                    viewScript = viewScript.ToUpper().Replace("PRIMARY KEY", "");
                }
                sbScript.AppendLine(viewScript);
            }

            #endregion
            System.IO.MemoryStream cvsOutPut = new System.IO.MemoryStream();
            byte[] data = Encoding.UTF8.GetBytes(sbScript.ToString());
            cvsOutPut.Write(data, 0, data.Length);
            return File(cvsOutPut.ToArray(), "text/plain", string.Format("CreateTableSql_{0}.sql", DateTime.Now.ToString("yyyyMMddHHmmss")));
        }

        #endregion

    }
}
